package util;


import java.io.File;   
import java.io.FileInputStream;   
import java.io.FileNotFoundException;   
import java.io.IOException;   
import java.util.ArrayList;
import java.util.Date;   
import java.util.HashMap;   
import java.util.List;
import java.util.Map;   
import org.apache.poi.hssf.usermodel.HSSFCell;   
import org.apache.poi.hssf.usermodel.HSSFRow;   
import org.apache.poi.hssf.usermodel.HSSFSheet;   
import org.apache.poi.hssf.usermodel.HSSFWorkbook;   
import org.apache.poi.poifs.filesystem.POIFSFileSystem;   
  
public class ExcelReader {   
    private POIFSFileSystem fs;   
    private HSSFWorkbook wb;   
    private HSSFSheet sheet;   
    private HSSFRow row;   
    private FileInputStream input;   
    private String[] excleTitle;   
    
    public HSSFSheet initSheet(String excelPath) {
    	try {   
            input = new FileInputStream(new File(excelPath));// excelPath  文件 的绝对路径   
            fs = new POIFSFileSystem(input);   
            wb = new HSSFWorkbook(fs);   
            sheet = wb.getSheetAt(0);   
        } catch (FileNotFoundException e) {   
            e.printStackTrace();   
        } catch (IOException e) {   
            e.printStackTrace();   
        }
        return sheet;
    }
    
    // 读取Excel表格表头的内容 
    public String[] readExcelTitle() {  
        row = sheet.getRow(0);// 得到标题的内容对象。   
        int colNum = row.getPhysicalNumberOfCells(); // 得到标题总列数   
        excleTitle = new String[colNum];   
        for (int i = 0; i < colNum; i++) {   
            excleTitle[i] = getStringCellValue(row.getCell((short) i));   
        }   
        
        return excleTitle;   
    }   
    
    // 读取Excel数据内容   
    public List<Map<Integer, String>> readExcelContent() {
    	List<Map<Integer, String>> list = new ArrayList<Map<Integer, String>>();
        String value = "";// excel 内容   
 
        int rowNum = sheet.getLastRowNum(); // 得到最后一行的行号,行号从0开始算起
        row = sheet.getRow(0);// 得到标题的内容对象。   
        int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。   
        for (int i = 1; i <= rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题   
            row = sheet.getRow(i);    
            Map<Integer, String> content = new HashMap<Integer, String>(); 
            for(int j = 0;j<colNum;j++) {
            	value = getStringCellValue(row.getCell((short) j)).trim();
            	content.put(j, value);
            	//System.out.println(i + "," + j + ":" + value);
            }   
            list.add(content);
        }   

        return list;   
    }   
    
    public List<Map<String, String>> readExcelContent(String[] titleName) {
    	List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        String value = "";// excel 内容   
 
        int rowNum = sheet.getLastRowNum(); // 得到最后一行的行号,行号从0开始算起
        row = sheet.getRow(0);// 得到标题的内容对象。   
        int colNum = row.getPhysicalNumberOfCells();// 得到每行的列数。   
        for (int i = 1; i <= rowNum; i++) { // 正文内容应该从第二行开始,第一行为表头的标题   
            row = sheet.getRow(i);    
            Map<String, String> content = new HashMap<String, String>(); 
            for(int j = 0;j<colNum;j++) {
            	value = getStringCellValue(row.getCell((short) j)).trim();
            	content.put(titleName[j], value);
            	//System.out.println(i + "," + j + ":" + value);
            }   
            list.add(content);
        }   

        return list;   
    }   
    
    private String getStringCellValue(HSSFCell cell) {// 获取单元格数据内容为字符串类型的数据   
        String strCell = "";   
        switch (cell.getCellType()) {   
        case HSSFCell.CELL_TYPE_STRING:   
            strCell = cell.getStringCellValue();   
            break;   
        case HSSFCell.CELL_TYPE_NUMERIC:   
            strCell = String.valueOf(cell.getNumericCellValue());   
            break;   
        case HSSFCell.CELL_TYPE_BOOLEAN:   
            strCell = String.valueOf(cell.getBooleanCellValue());   
            break;   
        case HSSFCell.CELL_TYPE_BLANK:   
            strCell = "";   
            break;   
        default:   
            strCell = "";   
            break;   
        }   
        if (strCell.equals("") || strCell == null) {   
            return "";   
        }   
        if (cell == null) {   
            return "";   
        }   
        return strCell;   
    }   
    private String getDateCellValue(HSSFCell cell) {// 获取单元格数据内容为日期类型的数据   
        String result = "";   
        try {   
            int cellType = cell.getCellType();   
            if (cellType == HSSFCell.CELL_TYPE_NUMERIC) {   
                Date date = cell.getDateCellValue();   
                result = (date.getYear() + 1900) + "-" + (date.getMonth() + 1)   
                        + "-" + date.getDate();   
            } else if (cellType == HSSFCell.CELL_TYPE_STRING) {   
                String date = getStringCellValue(cell);   
                result = date.replaceAll("[年月]", "-").replace("日", "").trim();   
            } else if (cellType == HSSFCell.CELL_TYPE_BLANK) {   
                result = "";   
            }   
        } catch (Exception e) {   
            System.out.println("日期格式不正确!");   
            e.printStackTrace();   
        }   
        return result;   
    }  
    
    public void close() {
    	try {   
            if (input != null) {   
                input.close();   
            }   
        } catch (IOException e) {   
            e.printStackTrace();   
        }  
    }
    
    public static void main(String[] args) {
		ExcelReader excelReader = new ExcelReader();
		excelReader.initSheet("E:/t.xls");
		
		//excelReader.readExcelContent();
		
		String[] titleName = new String[]{"code","name","unit","price","price2","remark"};
		excelReader.readExcelContent(titleName);
		
		excelReader.close();
	}
}  

